# validate_with_config.py
import time

import pandas as pd
from datetime import datetime
from upload_to_sql import safe_upload_to_dm
from VALIDATION_RULES import SHEET_VALIDATION_CONFIG, FIELD_VALIDATION_RULES
from dm_connection import connect_dm
import traceback



'''def validate_with_config(df, sheet_name, front_uuid):
    total_rows = len(df)
    result = []
    has_error = False

    #单独处理”人员安全档案“表
    if sheet_name == '人员安全档案':
        # 创建一个字典来记录每个值第一次出现的位置
        first_occurrence = {}

        for idx, value in df['员工卡号'].items():
            if value in first_occurrence :
                # 找到重复，记录当前行和第一次出现的行
                result.append({
                    'status': '失败',
                    'table_name': '人员安全档案',
                    'error_message': f"列 '员工卡号' 人员卡号重复: {value}",
                    'error_line_number': str(idx + 4),
                    'total_records': total_rows,
                    'record_time': datetime.now()
                })
                has_error = True
                break

            else:
                # 记录第一次出现的位置
                first_occurrence[value] = idx



    elif sheet_name in SHEET_VALIDATION_CONFIG and sheet_name != '人员安全档案':
        config = SHEET_VALIDATION_CONFIG[sheet_name]

        for col, rule_names in config.items():
            if col not in df.columns:
                has_error = True
                result.append({
                    'status': '失败',
                    'table_name': sheet_name,
                    'error_message': f"缺少必填列 '{col}'",
                    'error_line_number': '全部',
                    'total_records': total_rows,
                    'record_time': datetime.now()
                })
                continue

            for idx, val in df[col].items():
                for rule_name in rule_names:
                    try:
                        rule = FIELD_VALIDATION_RULES[rule_name]
                        kwargs = {}
                        if 'series' in rule['validate'].__code__.co_varnames:
                            kwargs['series'] = df[col]

                        if not rule['validate'](val, idx=idx, **kwargs):
                            has_error = True
                            result.append({
                                'status': '失败',
                                'table_name': sheet_name,
                                'error_message': f"列 '{col}' {rule['error_msg'](val)}",
                                'error_line_number': str(idx + 4),
                                'total_records': total_rows,
                                'record_time': datetime.now()
                            })
                    except Exception as e:
                        has_error = True
                        result.append({
                            'status': '失败',
                            'table_name': sheet_name,
                            'error_message': f"列 '{col}' 校验异常: {e}",
                            'error_line_number': str(idx + 4),
                            'total_records': total_rows,
                            'record_time': datetime.now()
                        })

    if not has_error:
        result.append({
            'status': '成功',
            'table_name': sheet_name,
            'error_message': '无',
            'error_line_number': '无',
            'total_records': total_rows,
            'record_time': datetime.now()
        })

    # ✅ 写入status表
    status_df = pd.DataFrame(result)

    # 打印 status_df 以确认是否有有效数据
    print("✅ 写入的 status 信息：")
    print(status_df)

    try:
        conn = connect_dm()
        safe_upload_to_dm(status_df, "status_information", conn, front_uuid,  mode='append')
        conn.close()
    except Exception as e:
        print("❌ 写入status表失败：", e)
        traceback.print_exc()

    return result'''



def validate_with_config(df, sheet_name, front_uuid):
    total_rows = len(df)
    result = []
    has_error = False

    if sheet_name in SHEET_VALIDATION_CONFIG:
        config = SHEET_VALIDATION_CONFIG[sheet_name]


        for col, rule_names in config.items():
            if col not in df.columns:
                has_error = True
                result.append({
                    'status': '失败',
                    'table_name': sheet_name,
                    'error_message': f"缺少必填列 '{col}'",
                    'error_line_number': '全部',
                    'total_records': total_rows,
                    'record_time': datetime.now()
                })
                continue

            for idx, val in df[col].items():
                for rule_name in rule_names:
                    try:
                        rule = FIELD_VALIDATION_RULES[rule_name]
                        kwargs = {}
                        if 'series' in rule['validate'].__code__.co_varnames:
                            kwargs['series'] = df[col]
                            # if sheet_name == '人员安全档案':
                            #     print('*'*100)
                            #     print(kwargs['series'])
                            #     print(idx)
                            #     print('*'*100)



                        if not rule['validate'](val, idx=idx, **kwargs):
                            has_error = True
                            result.append({
                                'status': '失败',
                                'table_name': sheet_name,
                                'error_message': f"列 '{col}' {rule['error_msg'](val)}",
                                'error_line_number': str(idx + 4),
                                'total_records': total_rows,
                                'record_time': datetime.now()
                            })
                    except Exception as e:
                        has_error = True
                        result.append({
                            'status': '失败',
                            'table_name': sheet_name,
                            'error_message': f"列 '{col}' 校验异常: {e}",
                            'error_line_number': str(idx + 4),
                            'total_records': total_rows,
                            'record_time': datetime.now()
                        })

    else:
        datetime_rule=FIELD_VALIDATION_RULES['datetime']
        #for col, rule_names in datetime_rule.items():
        col='数据日期'
        if '数据日期' not in df.columns:
                has_error = True
                result.append({
                    'status': '失败',
                    'table_name': sheet_name,
                    'error_message': f"缺少必填列 '{col}'",
                    'error_line_number': '全部',
                    'total_records': total_rows,
                    'record_time': datetime.now()
                })
               

        for idx, val in df['数据日期'].items():
            
                try:
                    rule = FIELD_VALIDATION_RULES['datetime']
                    kwargs = {}
                    if 'series' in rule['validate'].__code__.co_varnames:
                        kwargs['series'] = df['数据日期'] 

                    if not rule['validate'](val, idx=idx, **kwargs):
                        has_error = True
                        result.append({
                            'status': '失败',
                            'table_name': sheet_name,
                            'error_message': f"列 '{col}' {rule['error_msg'](val)}",
                            'error_line_number': str(idx + 4),
                            'total_records': total_rows,
                            'record_time': datetime.now()
                        })
                except Exception as e:
                    has_error = True
                    result.append({
                        'status': '失败',
                        'table_name': sheet_name,
                        'error_message': f"列 '{col}' 校验异常: {e}",
                        'error_line_number': str(idx + 4),
                        'total_records': total_rows,
                        'record_time': datetime.now()
                    })

    if not has_error:
        result.append({
            'status': '成功',
            'table_name': sheet_name,
            'error_message': '无',
            'error_line_number': '无',
            'total_records': total_rows,
            'record_time': datetime.now()
        })

    # ✅ 写入status表
    status_df = pd.DataFrame(result)

    # 打印 status_df 以确认是否有有效数据
    print("✅ 写入的 status 信息：")
    print(status_df)

    try:
        conn = connect_dm()
        safe_upload_to_dm(status_df, "status_information", conn, front_uuid,  mode='append')
        conn.close()
    except Exception as e:
        print("❌ 写入status表失败：", e)
        traceback.print_exc()

    return result



